BI - Instalação do Integration Services

Importante

É aconselhável instalar o componente Microsoft Integration Services na instalação do SQL Server porque caso deseje fazer isso mais tarde terá que reinstalar o SQL Server Novamente. Contudo posso instalar as ferramentas do Integration services fora do SQL Server, numa máquina de desenvolvimento qualquer e depois enviar o 'pacote' feito pela ferramenta (veja final desta página) e agendar sua execução no SQL Agent.

Utilidade

Se você é um cara muito bom em criar pesquisas, procs, etc. no Sql Server Management Studio, parabéns é um feito notável.
Já que você chegou num bom nível no SQL que tal fazer o mesmo com a sua ferramenta de trabalho?

Então guarde seu velho amigo SQL Server Management Studio no seu cinto de utilidades e conheça uma ferramenta desenhada para fazer os CRUDs, ETLs e o que for necessário ( como serviços de FTP ) para realizar suas tarefas no SQL...O Microsoft SQL Integration Services .

Em materia de recursos é um ganho significativo nas opções que você demoraria meses para implementar com o SQL Puro, se é que é possível.
Dentro do Integration Services, para tudo que você precisar a Microsoft fez um componente, um recurso que permite fazer o que você deseja de maneira muito mais elegante, visual e simplificada...muito mais amigável além de ser outro nível de execução de processos.

As origens - DTS

Se você é um brontosauro como eu deve-se lembrar das ferramentas de DTS (Data Transformation Services). Elas eram uma mão na roda para importar, exportar dados entre bases de dados dos mais diferentes tipos. Técnicamente qualquer manipulação de dados de uma fonte para um destino podia ser feito pelo DTS.

Mas tudo nessa vida evolui..e o DTS evoluiu para o SSIS ( SQL Server Integration Services ) onde foram criados 'componentes' capazes de buscar dados, transformar dados, salvar dados e incluiram eventos como no caso de erro como deve seguir o processo , etc.
É tão grande o leque de produto que seria impossível a citar todos eles mesmo que superficialmente. Por esse motivo dividi o assunto SSIS em diversas páginas, cada uma explicando um ponto separadamente.

Modo Brontossaurico

Se você é um analista de dados deve seguir mais ou menos a linha de trabalho que eu cito abaixo. Se seu processo for muito pior, fico contente em ajudá-lo.

De longa data desenvolvemos pesquisas no sql server que executam determinadas tarefas, como, por exemplo, carga de clientes novos, carga de vendas diárias, etc.

Contudo muitas vezes as necessidades da empresa ou do processo vão além de simplesmente executar uma pesquisa.

A primeira delas seria que fossem executadas automaticamente num determinado horário, com determinada repetição. Para isto foi criado o SQL Agent dentro do próprio MS SQL Server. Ele é capaz de executar qualquer pesquisa sem qualquer dificuldade e com ‘quase’ todos os recursos possíveis de tratamento de erro, agendamentos, etc. Mas nem tudo é um mar de rosas, como veremos abaixo.

A segunda necessidade seria documentar a pesquisa para que o dba faça um ajuste de performance no processo se necessário. Neste caso colocamos nossa pesquisa numa procedure e se esta procedure está consumindo muitos recursos do servidor o dba, tendo acesso a procedure, tem como fazer um acerto como criar um índice ou outro recurso, como alterar a procedure, de maneira que ela use menos recursos do servidor.

Mas e quando as coisas são um pouco mais complexas e precisamos ter melhor qualidade de processo ? Ninguém fica confortável quando um ‘dirigente da empresa’ chega pela manhã e informa que um dado que recebeu está errado e você não tem ideia do que ele está falando.

Uma das melhorias seria o tratamento de erros. A maneira mais comum é criar uma notificação de maneira que envie um e-mail automático em caso de erro. Se você for um bom desenvolvedor no próprio e-mail coloca as informações de erro de maneira que você não tenha que ficar ‘caçando’ o erro. Isto é feito por uma simples inclusão das cláusulas try-catch no seu processo.Mais ou menos assim:

BEGIN TRY
seus sqls....
END TRY
BEGIN CATCH
EXEC dbo.spEnviarEmailLogProcessos 'email@empresa.com.br', 'Falha task x', 'Etapa y - Nome Proc ou processo'
END CATCH

Digamos que o tratamento de erros é o zero a esquerda na automação de processos, o mínimo possível.
Mas tem uma coisa muito ruim no try-catch do sql server...ele só trapeia os erros do SQL Server, não do sistema operacional.
Explicando melhor, se houvesse um erro de conexão ( TCPIP ), um erro onde o serviço MS SQL Server o SQL Agent caísse ( como na chamada de um openrowset para um servidor Oracle remoto com o client Oracle com problemas ou autenticação/ segurança ), o try-catch não detectaria o problema e passaria batido pelo seu tratamento de erro, ou seja, o erro poderia passar batido ou nem mesmo prosseguir na execução da tarefa, pararia no comando que deu erro e o try-catch não detectaria o problema .

Com o try-catch seriamos informados da ocorrência do erro mas nenhum processo seria disparado para corrigir o erro ou notificar os gestores do serviço. .
Além disso um ‘dirigente da empresa’ não vai ficar feliz em saber que você sabe que ocorreu o erro, onde ocorreu o erro, nem quão rápido você consegue consertá-lo. Ele sempre irá querer que você tome uma providência para que ele sequer veja o erro, ou seja, que você conserte o erro antes que ele tome nota. Eles querem o mundo ideal que nós sabemos que não existe em TI.

A principal falha no SQL Agent é a inteligência no controle de processos. Por exemplo, se eu tenho um processo complexo onde muitas procedures são executadas como eu consigo fazer o ‘encadeamento’ do processo em uma task, por exemplo, de maneira que se a proc x anterior terminou com erro como fazemos que a proc y sucessora não seja executada já que depende da anterior?

Se você conhece bem o sql consegue implementar isso criando um processo de controle de execução que coordenaria a execução de todo o processo e ele chamaria os processos e em caso de erro você poderia chamar outros processos de correção ou mesmo re-executar os processos novamente. Digo aqui que já seria o caso de usar o SSIS mas vejamos que podemos fazer com martelo e talhadeira.

Outro nível de qualidade de processo temos quando sabemos que cada processo componente da tarefa foi executado com sucesso e no caso de erro , qual foi o processo que deu erro, linha, código de erro, mensagem do SQL.

Para termos esse 'controle' bastariamos guardar o resultado de cada processo dentro de uma tabela de log de maneira que cada processo colocasse seus resultados nela.
Em caso de sucesso colocaria 'Processo x terminado com sucesso'.
Em caso de erro colocasse uma mensagem 'Falha na execução do processo x', linha do erro, classe do erro, mensagem do sql, etc. Como sempre, a melhor solução para isso é criar uma tabela de log de erros, da seguinte maneira:

CREATE TABLE [dbo].[LogProcessos](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [processo] [nvarchar](50) NOT NULL,
    [etapa] [nvarchar](100) NULL,
    [procedimento] [nvarchar](300) NULL,
    [mensagem] [nvarchar](1000) NULL,
    [Email] [nvarchar](255) NULL,
    [Linha] [int] NULL,
    [numerro] [int] NULL,
    [severidade] [int] NULL,
    [estado] [int] NULL,
    [dtevento] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Cada campo dessa tabela tem uma utilidade única e fundamental. A primeira, o id serve para indicar uma sequencia, como os processos foram encadeados na tarefa.

A segunda coluna, processo, seria a identificação do nosso processo como um todo, por exemplo, carga de clientes que, dentro desse processo, contém diversas pesquisas, procs, etc. Então neste campo colocaríamos 'Processo de Carga de Clientes'.

Na terceira coluna, etapa, indicaríamos qual o passo da processo de carga de clientes emitiu a mensagem. Por exemplo, a carga de clientes tem 15 passos ou etapas...colocaríamos de 1 a 15 ou 'Etapa01-Limpeza da tabela Clientes'.

Na quarta coluna, procedimento, seria o nome da proc ( ERROR_PROCEDURE() ) ou da parte do processo que está sendo executada, como, sp_limpeza_tabClientes se fosse uma proc ou 'iniciando a limpeza da tabela de clientes' se fosse um sql comum.

Na quinta coluna, mensagem, teríamos ou 'SUCESSO' ou a mensagem de erro emitida pelo MS SQL Server (@@ERROR).

Na sexta coluna, Email seria o email da pessoa que deve ser alertada sobre a falha. Um outro processo consultaria a tabela de log de processos verificaria se algum processo deu erro pela mensagem e ai dispararia um email avisando sobre o problema ocorrido.

As quatro próximas colunas são exclusivas para a identificação onde ocorreu o erro. Com estas informações você saberá até a linha da proc que deu erro entre outras informações. Vejamos seu uso:

Campo	Variável @@
procedimento	ERROR_PROCEDURE()
Linha	ERROR_LINE()
numerro	ERROR_LINE()
severidade	ERROR_SEVERITY()
estado	ERROR_STATE()

Com essa tabela temos as seguintes informações gravadas pelas próprias procs. Reforçando o que disse acima :

1-processo: é o nome do processo que inseriu a informação. Por exemplo, tenho o processo de carga de clientes que executa 10 procs...então todas as mensagens das procs desse processo colocariam ‘Carga de Clientes’ neste campo.

2-Etapa: Cada proc executada pode ser definida como uma etapa. Portanto, se a carga de clientes tiver 10 procs eu posso simplesmente colocar de 1 a 10 nesse campo ou colocar ‘carga dos clientes do sistema x’ nesse campo. Identifica unicamente cada passo ou etapa executada pelo processo ‘Carga de Clientes’

3-Procedimento: Seria o nome da task ou do processo executado no servidor SQL em si para no caso de precisarmos fazer uma correção sabemos em que ponto fazer imediatamente. Em caso de erro normalmente terá o conteúdo de ERROR_PROCEDURE().

4-Mensagem: Costumo colocar ‘SUCESSO’ se não houve erro ou a mensagem de erro caso ocorra um erro. Em caso de erro normalmente terá o valor de ERROR_MESSAGE()

5-Email: Caso a empresa seja grande e os processos gerenciados sejam multidisciplinares você poderia colocar neste campo o email de quem deve receber o e-mail no caso de falha. Em caso de Sucesso eu não colocaria nada neste campo...não quero importunar ninguém a não ser que ele peça.

6-Linha,Número do erro, Severidade, Estado são campos que ajudam na depuração do erro ocorrido. Seriam basicamente os campos: ERROR_LINE(), ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE()

7-Data do Evento: Um erro pode ocorrer e ao reprocessar pode voltar a ocorrer...gostaria de saber que são 2 eventos distintos e esse é a finalidade deste campo. Basicamente conterá o campo GETDATE().

Normalmente ao inserir uma mensagem de erro numa etapa do processo a gente chamaria uma proc para envio de email : spEnviarEmailLogProcessos. Contudo esta solução faria com recebêssemos muitos emails em caso de tratamento e recuperação de falhas.

Essa seria a solução mais simples que qualquer desenvolvedor de pesquisas SQL poderia fazer. Mas tem ferramenta bem melhor para trabalhar.

Com isso a nossa rotina de tratamento de erros, o try-catch ficaria assim:
BEGIN TRY
seus sqls....
END TRY
BEGIN CATCH
insert into LogProcessos(processo, etapa, procedimento, linha, mensagem, numerro, severidade, estado, email, dtEvento)
values ( 'Processo Carga de Clientes', 'Etapa x', ERROR_PROCEDURE(), ERROR_LINE(), ERROR_MESSAGE(), ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), 'email@empresa.com.br', GETDATE())
EXEC dbo.spEnviarEmailLogProcessos 'email@empresa.com.br', 'Falha task Carga Clientes', 'Etapa x - Proc NomeProc'
END CATCH

Proc envio Emails

Caso deseje conhecer a proc que envia emails ela é a seguinte:

/*
Autor: x
Data de criação: y
Descrição: Proc para envio de email
select * from logprocessos
truncate table logprocessos
Nota : As colunas e seus conteúdos estão chumbadas no código desta proc
e devem bater exatamente com o select das colunas da tabela logProcessos

Nota : o insert na tabela log de processos tem muitos campos :
insert into LogProcessos (processo,etapa,procedimento,mensagem,Email,Linha,numerro,severidade,estado,dtevento) values (
'Processo x',1,'Etapa 1-Carga Clientes','Processo Iniciado','email@empresa.com.br',0,0,0,0,getdate())
mas o envio de email não...só destinatário e o assunto

EXEC dbo.spEnviarEmailLogProcessos 'Proc Carga Clientes', 'Teste de Envio de Email'
apenas o assunto é enviado
o destinatário será definido pelo nome do processo
a @mensagem varchar(1000) é o próprio conteúdo da tabela logProcessos
Cuidado: O ID do processo define o destinatário
*/
CREATE PROCEDURE dbo.spEnviarEmailLogProcessos
@processo varchar(100),
@assunto varchar(200)

AS BEGIN
SET NOCOUNT ON;

--buscando o destinatário dos emails do processo
declare @emaildestinatario varchar(100)
select @emaildestinatario = destinatario from tblDestinatariosEmail where processo = @processo

--print @emaildestinatario
--return

if len(@emaildestinatario) < 10 begin
  insert into LogProcessos (processo,etapa,procedimento,mensagem,Email,Linha,numerro,severidade,estado,dtevento) values (
  'spEnviarEmailLogProcessos',1,'Etapa 1-Envio Email','Falha Destinatário Email','email@empresa.com.br',0,0,0,0,getdate())
  return -- fim da proc
end
DECLARE @TABLEHMTML NVARCHAR(MAX)

SET @TABLEHMTML = N'<h1>' + @assunto + '</h1>'+
N'<table border="1" style="border: 1px solid black; border-collapse: collapse;font-family:Calibri;font-size:small;border-color:blue" cellpadding="5" >'+
N'<tr style="background-color:lightblue"><th>Num.</th><th>Processo</th><th>Etapa</th><th>Procedimento</th><th>Mensagem</th><th>E-mail</th><th>Linha</th><th>NumErro</th><th>Severidade</th><th>Estado</th><th>Data</th></tr>'+
CAST(( SELECT td = id,'',
  td = processo, '',
  td = etapa,'',
  td = procedimento, '',
  td = mensagem, '',
  td = email, '',
  td = linha, '',
  td = numerro, '',
  td = severidade, '',
  td = estado, '',
  td = dtEvento
FROM logprocessos
FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX)) + N'</table>';

EXEC msdb.DBO.sp_send_dbmail @recipients = @emaildestinatario,
@subject=@emaildestinatario,
@body = @TABLEHMTML,
@body_format ='HTML';

END

Essa proc podemos implementar de 2 maneiras...cada processo tem um destinatário ou cada etapa tem um destinatário.
Quando eu criei a tabela de log eu fiz para o pior caso, cada etapa tem um destinatário.
Mas muitas vezes há um único destinatário para todos os processos. Para isso criei a tabela tblDestinatariosEmail que casa um processo ( e todas as etapas dele ) a um único destinatário. Fiz isso porque caso precise mudar os destinatários dos emails eu não precise entrar em cada proc e alterar o destinatário, uma a uma.

Estrutura da tabela destinatários de email

CREATE TABLE [dbo].[tblDestinatariosEmail](
[processo] [varchar](100) NULL,
[destinatario] [varchar](100) NULL
) ON [PRIMARY]

GO

Conteúdo da tabela destinatários de email

processo	Destinatario
processo x	destino1@empresa.com.br
processo y	destino2@empresa.com.br
processo z	destino3@empresa.com.br

2- Integration Services

Para criar projetos com o Integration Services você precisa de uma ferramenta a altura que trabalhe com o Data Transformation Services chamada de Data Transformation TOOLS.

A minha predileta, já que sou um desenvolvedor, é o Visual Studio Community Edition. É a ferramenta 'bom-bril' da Microsoft que serve para tudo. Para instalá-la vá no Google e procuere por ‘Visual Studio Community Edition’ e instale a versão mais recente. É gratuita e open source.

Ainda estamos 'aguçando nossas ferramentas' ou preparando nossa cozinha para o prato principal.
Feito isso abra o Visual Studio e clique no menu ’Extensões’.
Selecione ‘Gerenciar Extensões’.
Na caixa pesquisar digite Integration Services
Instale o componente.
Note que uma vez instalado e incluído no Visual Studio você não precisará instalar ele novamente num novo projeto, basta usar.

E em seguida clique no botão ‘Baixar’ para instalar a ferramenta.
Baixada a ferramenta faça a instalação..será mais ou menos assim

Feito isso sua ferramenta está pronta para trabalhar. A seguir temos 2 páginas que podem ser de seu interesse:

1-Componentes dos projetos SSIS. Neste documento enumero os componentes mas são tantos que não ouso tentar explicar todos, menciono apenas para mencionar o que existe e que esses componentes devem atender o que você deseja.

2-Como criar um projeto SSIS. Nesse projeto menciono passo a passo como importar um arquivo texto para uma tabela do MS SQL Server.